﻿using DevelopTool.SqlAnalyze.Util;
using SharpSqlAnalyzer;
using SharpSqlAnalyzer.nodes;
using SharpSqlAnalyzer.stmt;
using SharpSqlAnalyzer.stmt.oracle;
using System;
using System.Collections.Generic;

namespace DevelopTool.SqlAnalyze
{
    public class Analyzesp
    {
        private readonly EDbVendor eDbVendor;

        public Analyzesp(EDbVendor eDbVendor = EDbVendor.dbvoracle)
        {
            this.eDbVendor = eDbVendor;
        }

        /// <summary>
        /// 获取sql分析结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public ProcedureInfo GetResult(string sql)
        {
            TGSharpSqlParser sqlparser = new TGSharpSqlParser(eDbVendor);
            sqlparser.sqltext = sql;
            int ret = sqlparser.parse();
            if (ret != 0) throw new Exception("未能识别sql文本，请检查语法是否正确");

            ProcedureInfo procedureInfo = new ProcedureInfo();

            for (int i = 0; i < sqlparser.sqlstatements.size(); i++)
            {
                TCustomSqlStatement stmt = sqlparser.sqlstatements.get(i);

                if (stmt is TSelectSqlStatement)
                {
                    SelectSqlStatement((TSelectSqlStatement)stmt, procedureInfo);
                }
                else if (stmt is TInsertSqlStatement)
                {
                    InsertSqlStatement((TInsertSqlStatement)stmt, procedureInfo);
                }
                else if (stmt is TUpdateSqlStatement)
                {
                    UpdateSqlStatement((TUpdateSqlStatement)stmt, procedureInfo);
                }
                else if (stmt is TDeleteSqlStatement)
                {
                    DeleteSqlStatement((TDeleteSqlStatement)stmt, procedureInfo);
                }
                else if (stmt is TOracleExecuteProcedure)
                {
                    OracleExecuteProcedure((TOracleExecuteProcedure)stmt, procedureInfo);
                }
                else if (stmt is TCreateTableSqlStatement)
                {
                    CreateTableSqlStatement((TCreateTableSqlStatement)stmt, procedureInfo);
                }
            }
            return procedureInfo;
        }

        private void CreateTableSqlStatement(TCreateTableSqlStatement createStmt, ProcedureInfo procedureInfo)
        {
            TColumnDefinitionList columns = createStmt.ColumnList;
            OperateInfo operateInfo = new OperateInfo();
            operateInfo.ObjectType = ObjectType.Table;
            operateInfo.TableName = createStmt.TargetTable.ToString().Trim();
            operateInfo.UsageType = UsageType.Create;
            for (int i = 0; i < columns.size(); i++)
            {
                TColumnDefinition column = columns.getColumn(i);
                operateInfo.Columns.Add(new Column() { ColumnName = column.ColumnName.ToString() });
            }
            procedureInfo.OperateInfos.Add(operateInfo);
        }


        private void OracleExecuteProcedure(TOracleExecuteProcedure executeStmt, ProcedureInfo procedureInfo)
        {
            OperateInfo operateInfo = new OperateInfo();
            operateInfo.ObjectType = ObjectType.SP;
            operateInfo.TableName = executeStmt.ProcedureName.ToString().Trim();
            operateInfo.UsageType = UsageType.Exec;
            procedureInfo.OperateInfos.Add(operateInfo);
        }


        private void DeleteSqlStatement(TDeleteSqlStatement deleteStmt, ProcedureInfo procedureInfo)
        {
            OperateInfo operateInfo = new OperateInfo();
            operateInfo.TableName = deleteStmt.TargetTable.ToString().Trim();
            operateInfo.UsageType = UsageType.Delete;
            procedureInfo.OperateInfos.Add(operateInfo);
        }

        private void UpdateSqlStatement(TUpdateSqlStatement updateStmt, ProcedureInfo procedureInfo)
        {
            TResultColumnList columns = updateStmt.ResultColumnList;
            OperateInfo operateInfo = new OperateInfo();
            operateInfo.TableName = updateStmt.TargetTable.ToString().Trim();
            operateInfo.UsageType = UsageType.Update;
            for (int i = 0; i < columns.size(); i++)
            {
                TResultColumn column = columns.getResultColumn(i);
                operateInfo.Columns.Add(new Column() { ColumnName = column.Expr.LeftOperand.ToString() });
            }
            procedureInfo.OperateInfos.Add(operateInfo);
        }


        private void InsertSqlStatement(TInsertSqlStatement insertStmt, ProcedureInfo procedureInfo)
        {
            TObjectNameList columns = insertStmt.ColumnList;
            OperateInfo operateInfo = new OperateInfo();
            operateInfo.TableName = insertStmt.TargetTable.ToString().Trim();
            operateInfo.UsageType = UsageType.Insert;
            if (columns != null)
            {
                for (int i = 0; i < columns.size(); i++)
                {
                    TObjectName column = columns.getObjectName(i);
                    operateInfo.Columns.Add(new Column() { ColumnName = column.ToString() });
                }
            }
            procedureInfo.OperateInfos.Add(operateInfo);
        }


        private void SelectSqlStatement(TSelectSqlStatement selectStmt, ProcedureInfo procedureInfo)
        {
            List<ColumnInfo> columnInfos = new List<ColumnInfo>();
            List<TableInfo> tableInfos = new List<TableInfo>();
            tableTokensInStmt(columnInfos, tableInfos, selectStmt);
            LinkedHashMap<TableInfo, List<ColumnInfo>> columnMap = new LinkedHashMap<TableInfo, List<ColumnInfo>>();
            for (int j = 0; j < columnInfos.Count; j++)
            {
                ColumnInfo column = columnInfos[j];
                TableInfo table = column.table;
                if (columnMap.ContainsKey(table))
                {
                    List<ColumnInfo> columns = (List<ColumnInfo>)columnMap[table];
                    bool flag = false;
                    foreach (ColumnInfo temp in columns)
                    {
                        if (temp.ToString().Equals(column.ToString(), StringComparison.CurrentCultureIgnoreCase))
                        {
                            flag = true;
                            break;
                        }
                    }
                    if (!flag)
                    {
                        columns.Add(column);
                    }
                }
                else
                {
                    List<ColumnInfo> columns = new List<ColumnInfo>();
                    columnMap[table] = columns;
                    columns.Add(column);
                }
            }
            for (int k = 0; k < tableInfos.Count; k++)
            {
                OperateInfo operateInfo = new OperateInfo();
                operateInfo.TableName = tableInfos[k].ToString();
                if (tableInfos[k].stmt is TSelectSqlStatement && ((TSelectSqlStatement)tableInfos[k].stmt).IntoClause != null)
                {
                    operateInfo.UsageType = UsageType.Insert;
                }
                else
                {
                    operateInfo.UsageType = UsageType.Select;
                }
                if (columnMap.ContainsKey(tableInfos[k]))
                {
                    foreach (ColumnInfo column in (List<ColumnInfo>)columnMap[tableInfos[k]])
                    {
                        operateInfo.Columns.Add(new Column() { ColumnName = column.ToString() });
                        operateInfo.TableName = column.table.ToString();
                    }
                }
                procedureInfo.OperateInfos.Add(operateInfo);
            }
        }



        private void tableTokensInStmt(List<ColumnInfo> columnInfos, List<TableInfo> tableInfos, TCustomSqlStatement stmt)
        {
            for (int i = 0; i < stmt.tables.size(); i++)
            {
                if (stmt.tables.getTable(i).BaseTable)
                {
                    if (stmt.tables.getTable(i).EffectType == ETableEffectType.tetSelectInto)
                    {
                        continue;
                    }

                    TableInfo tableInfo = new TableInfo();
                    tableInfo.fullName = stmt.tables.getTable(i).FullName;
                    tableInfos.Add(tableInfo);

                    for (int j = 0; j < stmt.tables.getTable(i).LinkedColumns.size(); j++)
                    {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.table = tableInfo;
                        columnInfo.column = stmt.tables.getTable(i).LinkedColumns.getObjectName(j);
                        columnInfos.Add(columnInfo);
                    }
                }
            }

            if (stmt is TSelectSqlStatement && ((TSelectSqlStatement)stmt).IntoClause != null)
            {
                TExpressionList tables = ((TSelectSqlStatement)stmt).IntoClause.ExprList;
                for (int j = 0; j < tables.size(); j++)
                {
                    TableInfo tableInfo = new TableInfo();
                    tableInfo.fullName = tables.getExpression(j).ToString();
                    tableInfo.stmt = stmt;
                    tableInfos.Add(tableInfo);
                }
            }

            for (int i = 0; i < stmt.Statements.size(); i++)
            {
                tableTokensInStmt(columnInfos, tableInfos, stmt.Statements.get(i));
            }
        }

    }

    public class ProcedureInfo
    {
        public List<OperateInfo> OperateInfos { get; set; } = new List<OperateInfo>();

    }


    public class OperateInfo
    {
        public int Index { get; set; }

        public ObjectType ObjectType { get; set; } = ObjectType.Table;

        /// <summary>
        /// 对象名（表名）
        /// </summary>
        public string TableName { get; set; }


        /// <summary>
        /// 操作类型
        /// </summary>
        public UsageType UsageType { get; set; }


        /// <summary>
        /// 表列（字段）集合
        /// </summary>
        public List<Column> Columns { get; set; } = new List<Column>();

        /// <summary>
        /// 操作时间
        /// </summary>
        public string Timestamp { get; set; }

        /// <summary>
        /// 执行耗时
        /// </summary>
        public string ElapsedTime { get; set; }

        public string Sql { get; set; }

        /// <summary>
        /// 表注释
        /// </summary>
        public string Comments { get; set; } = string.Empty;
    }

    public class Column
    {
        /// <summary>
        /// 字段名
        /// </summary>
        public string ColumnName { get; set; }

        /// <summary>
        /// 字段注释
        /// </summary>
        public string Comments { get; set; } = string.Empty;

        /// <summary>
        /// 数据类型
        /// </summary>
        public string DataType { get; set; }

        /// <summary>
        /// 列长度
        /// </summary>
        public string DataLength { get; set; }

    }

    /// <summary>
    /// 操作类型 枚举
    /// </summary>
    public enum UsageType
    {
        /// <summary>
        /// 查询
        /// </summary>
        Select,

        /// <summary>
        /// 新增
        /// </summary>
        Insert,

        /// <summary>
        /// 更新
        /// </summary>
        Update,

        /// <summary>
        /// 删除
        /// </summary>
        Delete,

        /// <summary>
        /// 存储过程执行
        /// </summary>
        Exec,
        /// <summary>
        /// 未知
        /// </summary>
        Unknown,

        /// <summary>
        /// 创建
        /// </summary>
        Create
    }

    public enum ObjectType
    {
        SP,
        Table
    }


    class ColumnInfo
    {

        public TableInfo table { get; set; }

        public TObjectName column { get; set; }

        public override string ToString()
        {
            return column == null ? "" : column.ColumnNameOnly.Trim();
        }
    }

    class TableInfo
    {

        public string fullName { get; set; }

        public TCustomSqlStatement stmt { get; set; }

        public override string ToString()
        {
            return (string.ReferenceEquals(fullName, null) ? "" : fullName.Trim());
        }

    }



}
